CREATE VIEW [dbo].[vRecurringDonationExpectedPaymentSetList]
AS
    SELECT
        [e].[RecurringDonationExpectedPaymentSetKey],
        [e].[UpdatedOn] as [DateRun],
        [e].[ExpectedPaymentDate] as [MonthYear],
        [s].[RecurringDonationExpectedPaymentSetStatusDesc] as [RunStatus],
        (SELECT ISNULL(SUM([p].[DonationAmount]),0) FROM [RecurringDonationExpectedPayment] [p] WHERE [p].[RecurringDonationExpectedPaymentSetKey] = [e].[RecurringDonationExpectedPaymentSetKey] and [p].[RecurringDonationExpectedPaymentStatusCode] = 2) as [AmountWaiting],
        (SELECT COUNT(0) FROM [RecurringDonationExpectedPayment] [p] WHERE [p].[RecurringDonationExpectedPaymentSetKey] = [e].[RecurringDonationExpectedPaymentSetKey] and [p].[RecurringDonationExpectedPaymentStatusCode] = 2) as [NumberWaiting],
        (SELECT ISNULL(SUM([p].[DonationAmount]),0) FROM [RecurringDonationExpectedPayment] [p] WHERE [p].[RecurringDonationExpectedPaymentSetKey] = [e].[RecurringDonationExpectedPaymentSetKey] and [p].[RecurringDonationExpectedPaymentStatusCode] = 3) as [AmountProcessed],
        (SELECT COUNT(0) FROM [RecurringDonationExpectedPayment] [p] WHERE [p].[RecurringDonationExpectedPaymentSetKey] = [e].[RecurringDonationExpectedPaymentSetKey] and [p].[RecurringDonationExpectedPaymentStatusCode] = 3) as [NumberProcessed]
    FROM [dbo].[RecurringDonationExpectedPaymentSet] e
    INNER JOIN [dbo].[RecurringDonationExpectedPaymentSetStatusRef] s ON [e].[RecurringDonationExpectedPaymentSetStatusCode] = [s].[RecurringDonationExpectedPaymentSetStatusCode]



GO
GRANT SELECT ON  [dbo].[vRecurringDonationExpectedPaymentSetList] TO [IMIS]
GO